package com.gamebox.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.swing.JTextField;

import com.gamebox.javaBean.Game;
import com.gamebox.javaBean.Remark;
import com.gamebox.javaBean.User;
import com.gamebox.util.DBDriver;
import com.gamebox.util.DBTool;

public class GameDao {
	public List<String> getFriendReq(int tos){
		String sql = "select * from friends where tos="+tos+" and state="+0;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return null;
		}
		List<String> strList = new ArrayList<String>();
		try {
			while(result.next()){
				int fromsid = result.getInt("froms");
				User fuser = getUser(fromsid);
				strList.add(fuser.getNickName()+" 请求添加你为好友");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return strList;
	
	}
	public void addFriend(int fromId,int toId){
		String sql = "INSERT INTO friends(froms,tos,state) VALUES(?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,fromId);
			pstmt.setInt(2, toId);
			pstmt.setInt(3, 0);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addFriend2(int fromId,int toId){
		String sql = "INSERT INTO friends(froms,tos,state) VALUES(?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,fromId);
			pstmt.setInt(2, toId);
			pstmt.setInt(3, 1);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addRemark(int userId,int gameId,String content){
		String sql = "INSERT INTO remark(userId,gameId,content) VALUES(?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,userId);
			pstmt.setInt(2, gameId);
			pstmt.setString(3, content);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void addMark(int userId,int gameId,String content){
		String sql = "INSERT INTO mark(userId,gameId,mark) VALUES(?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,userId);
			pstmt.setInt(2, gameId);
			pstmt.setInt(3, Integer.parseInt(content));
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void updateGameMark(Game game,int mark){
		int gameId = game.getId();
		String sql = "select * from mark where gameId="+gameId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		try {
			int score = 0;
			int num = 0;
			while(result.next()){
				score += result.getInt("mark");
				num++;
			}
			if(num!=0){
				score = score/num;
			}
			sql = "update game set score ="+score+" where id="+gameId;
			dbTool = new DBTool();
			dbTool.excuteUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void deleteSaveRecord(int userId,int gameId){
		String sql = "delete  from saveRecord where userId ="+userId+" and gameId="+gameId;
		DBTool dbTool = new DBTool();
		dbTool.excuteDelete(sql);
	}
	public void addTalk(int from,int to,String content){
		String sql = "INSERT INTO talk(froms,tos,content) VALUES(?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,from);
			pstmt.setInt(2, to);
			pstmt.setString(3, content);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public List<String> getComFri(int userId,String nickName){
		User u1 = getUserByNick(nickName);
		List<String> l1 = getMyFriends(userId);
		l1.retainAll(getMyFriends(u1.getId()));
		return l1;
	}
	public User getUserByNick(String nick){
		String sql = "select * from user where nickname ='"+nick+"'";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return null;
		}
		try {
			if(result.next()){
				int id = result.getInt("id");
				String username = result.getString("username");
				String upassword = result.getString("password");
				int type = result.getInt("type");
				String nickName = result.getString("nickname");
				Date registerTime = result.getDate("registerTime");
				int groupId = result.getInt("groupId");
				return new User(id,username,upassword,type,nickName,groupId,registerTime);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public List<String> getMyTalk(int userId){
		List<String> temp = new ArrayList<String>();
		String sql = "select * from talk t where t.froms="+userId+" or t.tos="+userId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return temp;
		}
		try {
			while(result.next()){
				int from = result.getInt("froms");
				int to = result.getInt("tos");
				String content = result.getString("content");
				temp.add(getUser(from).getNickName()+" 对 "+getUser(to).getNickName()+" 说 "+content);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}
	public User getUser(int userId){
		String sql = "select * from user where id="+userId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return null;
		}
		try {
			if(result.next()){
				int id = result.getInt("id");
				String username = result.getString("username");
				String upassword = result.getString("password");
				int type = result.getInt("type");
				String nickName = result.getString("nickname");
				Date registerTime = result.getDate("registerTime");
				int groupId = result.getInt("groupId");
				return new User(id,username,upassword,type,nickName,groupId,registerTime);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public List<String> getMyFriends(int userId){
		List<String> temp = new ArrayList<String>();
		String sql = "select * from user a, friends b where a.id = b.froms and b.tos="+userId+" and b.state=1";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return temp;
		}
		try {
			while(result.next()){
				temp.add(result.getString("a.nickname"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return temp;
	}
	public int getGameNum(int userId){
		String sql = "select count(*) from playrecord where userId ="+userId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		int i=0;
		if(result == null){
			return i;
		}
		try {
			if(result.next()){
				i = result.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public List<String> getGroups(){
		List<String> list =new ArrayList<String>();
		String sql = "select * from groups";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if(result == null){
			return list;
		}
		try {
			while(result.next()){
				list.add(result.getString("name"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	public int addGame(Game game) {
		String sql = "INSERT INTO game(name,path,label,playerNum,score,clickNum) VALUES(?,?,?,?,?,?)";
		Connection conn = DBDriver.getConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, game.getName());
			pstmt.setString(2, game.getPath());
			pstmt.setString(3, game.getLabel());
			pstmt.setInt(4, game.getPlayerNum());
			pstmt.setInt(5, game.getScore());
			pstmt.setInt(6, game.getClickNum());
			pstmt.execute();
			sql = "select max(id) as gid  from game";
			DBTool dbTool = new DBTool();
			ResultSet result = dbTool.excuteSelect(sql);
			if(result.next()){
				return result.getInt("gid");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	public void addSaveRecord(int userId,int gameId){
		String sql = "select * from saverecord where userId = "+userId +" and gameId = "+gameId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		try {
			if(!result.next()){
				sql = "insert into saverecord(userId,gameId) values(?,?)";
				Connection conn = DBDriver.getConnection();
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, userId);
				pstmt.setInt(2, gameId);
				pstmt.execute();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ArrayList<Game> getAllGames(User user) {
		ArrayList<Game> games = new ArrayList<Game>();

		String sql = "select * from saverecord where userId = " + user.getId();
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while (result.next()) {
				int id = result.getInt("gameId");
				Game game = getGameById(id);
				games.add(game);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return games;
	}
	public Game getGameById(int id){
		String sql = "select * from game where id = "+id;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		Game game = null;
		try {
			if(result.next()){
				int gid = result.getInt("id");
				String name = result.getString("name");
				String path = result.getString("path");
				String label = result.getString("label");
				int playerNum = result.getInt("playerNum");
				int score = result.getInt("score");
				int clickNum = result.getInt("clickNum");
				game = new Game(gid,name,label,path,clickNum,playerNum,score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return game;
	}
	public List<Game> getGamesByName(String name){
		ArrayList<Game> games = new ArrayList<Game>();
		String sql = "select * from game where name = '"+name+"'";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while(result.next()){
				int id = result.getInt("id");
				String gname = result.getString("name");
				String label = result.getString("label");
				String path = result.getString("path");
				int clickNum = result.getInt("clickNum");
				int score = result.getInt("score");
				int playerNum = result.getInt("playerNum");
				Game game = new Game(id,gname,label,path,clickNum,playerNum,score);
				games.add(game);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return games;
	}
	public List<Remark> getRemarkList(int gameId){
		List<Remark> remarkList = new ArrayList<Remark>();
		String sql = "select a.nickName , b.content from user a, remark b where a.id = b.userId and b.gameId = "+gameId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			return remarkList;
		}
		try {
			while(result.next()){
				String nickName = result.getString(1);
				String content = result.getString(2);
				Remark remark = new Remark(nickName,content);
				remarkList.add(remark);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return remarkList;
	}
	public List<User> getPlayerList(int gameId){
		List<User> playerList = new ArrayList<User>();
		String sql = "select * from user a ,playrecord b where  a.id = b.userId and b.gameId = "+gameId;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			return playerList;
		}
		try {
			while(result.next()){
				int id = result.getInt("a.id");
				String username = result.getString("a.username");
				String password = result.getString("a.password");
				int type = result.getInt("a.type");
				String nickName = result.getString("a.nickname");
				Date registerTime = result.getDate("a.registerTime");
				int groupId = result.getInt("a.groupId");
				User user = new User(id,username,password,type,nickName,groupId,registerTime);
				playerList.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return playerList;
	}
	public String getNameByGId(int id){
		String sql = "select * from groups where id="+id;
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		String s = "all";
		if(result == null){
			return s;
		}
		try {
			if(result.next()){
				s = result.getString("name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return s;
	}
	public ArrayList<Game> getAllGames(){

		ArrayList<Game> games = new ArrayList<Game>();

		String sql = "select * from game";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while (result.next()) {
				int id = result.getInt("id");
				Game game = getGameById(id);
				games.add(game);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return games;
	
	}
	public ArrayList<Game> getAllGamesByClickNum(){

		ArrayList<Game> games = new ArrayList<Game>();

		String sql = "select * from game order by clickNum desc";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while (result.next()) {
				int id = result.getInt("id");
				Game game = getGameById(id);
				games.add(game);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return games;
	
	}
	public ArrayList<Game> getAllGamesByPlayNum(){

		ArrayList<Game> games = new ArrayList<Game>();

		String sql = "select * from game order by playerNum desc";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while (result.next()) {
				int id = result.getInt("id");
				Game game = getGameById(id);
				games.add(game);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return games;
	
	}
	public ArrayList<Game> getAllGamesByScore(){

		ArrayList<Game> games = new ArrayList<Game>();

		String sql = "select * from game order by score desc";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while (result.next()) {
				int id = result.getInt("id");
				Game game = getGameById(id);
				games.add(game);
			}

		} catch (Exception e) {
			// TODO: handle exception
		}
		return games;
	
	}
	public ArrayList<Game> getFuzzyGamesByName(String name, User user) {
		// TODO Auto-generated method stub

		ArrayList<Game> games = new ArrayList<Game>();
		String sql = "select * from game a,saveRecord b where a.name like '%"+name+"%' and a.id = b.gameId and b.userId ="+user.getId();
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while(result.next()){
				int id = result.getInt("id");
				String gname = result.getString("name");
				String label = result.getString("label");
				String path = result.getString("path");
				int clickNum = result.getInt("clickNum");
				int score = result.getInt("score");
				int playerNum = result.getInt("playerNum");
				Game game = new Game(id,gname,label,path,clickNum,playerNum,score);
				games.add(game);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return games;
	
	}
	public ArrayList<Game> getGamesByLabel(String name, User user) {
		// TODO Auto-generated method stub

		ArrayList<Game> games = new ArrayList<Game>();
		String sql = "select * from game a,saveRecord b where a.label='"+name+"' and a.id = b.gameId and b.userId ="+user.getId();
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		if (result == null) {
			System.out.println("null");
			return games;
		}
		try {
			while(result.next()){
				int id = result.getInt("id");
				String gname = result.getString("name");
				String label = result.getString("label");
				String path = result.getString("path");
				int clickNum = result.getInt("clickNum");
				int score = result.getInt("score");
				int playerNum = result.getInt("playerNum");
				Game game = new Game(id,gname,label,path,clickNum,playerNum,score);
				games.add(game);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return games;
	
	}
	public void updateFriend(int from, int to) {
		// TODO Auto-generated method stub
		String sql = "update friends set state=1 where froms="+from+" and tos="+to;
		DBTool dbTool = new DBTool();
		dbTool.excuteDelete(sql);
	}
	public void deleteUser(int id) {
		// TODO Auto-generated method stub
		String sql = "delete  from user where id ="+id;
		DBTool dbTool = new DBTool();
		dbTool.excuteDelete(sql);
	}
	public void updateInfo(String nick, String pass1String,int userId) {
		// TODO Auto-generated method stub
		String sql = "update user set nickname='"+nick+"',password='"+pass1String+"' where id="+userId;
		DBTool dbTool = new DBTool();
		dbTool.excuteUpdate(sql);
	}
	public int getGroupByName(String groupName) {
		// TODO Auto-generated method stub
		String sql = "select * from groups where name='"+groupName+"'";
		DBTool dbTool = new DBTool();
		ResultSet result = dbTool.excuteSelect(sql);
		int i = 0;
		try {
			if(result.next()){
				i = result.getInt("id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public void updateGroup(int id, int groupId) {
		// TODO Auto-generated method stub
		String sql = "update user set groupId="+groupId+" where id="+id;
		DBTool dbTool = new DBTool();
		dbTool.excuteUpdate(sql);
	}
	public void updateGameClickNum(Game game) {
		// TODO Auto-generated method stub
		String sql = "update game set clickNum=clickNum+1 where id="+game.getId();
		DBTool dbTool = new DBTool();
		dbTool.excuteUpdate(sql);
	}
	public void updateGamePlayerNum(Game game) {
		// TODO Auto-generated method stub
		String sql = "update game set playerNum=playerNum+1 where id="+game.getId();
		DBTool dbTool = new DBTool();
		dbTool.excuteUpdate(sql);
	}
	public void addPlayRecord(int id, int id2) {
		// TODO Auto-generated method stub
		String sql = "insert into playRecord(userId,gameId) values(?,?)";
		Connection conn = DBDriver.getConnection();
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, id2);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
